package com.haiertvbic.tvnow.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.haiertvbic.tvnow.model.ProgramDetail;
import com.haiertvbic.tvnow.model.ProgramItem;
import com.haiertvbic.tvnow.util.SLog;
import com.haiertvbic.tvnow.util.TimeUtil;

public class DbAdapter {
	private static final String TAG = "DbAdapter";
	private Context context;
	private DBHelper dbHelper;
	private SQLiteDatabase db;

	public void getInstance(Context context) {
		this.context = context;
		if (dbHelper == null) {
			dbHelper = new DBHelper(context, "tvnowDB.db", null, 4);

		}
	}

	public synchronized void addPrograms(ArrayList<ProgramDetail> programs) {
		db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		Object[] bindArgs = null;
		// 共计17个字段，20130428
		// 共计18个字段，20130530；增加集数字段
		String sql = "insert into programs (pid,pweekday,date,lastupdate,pstime,pftime,pn,cname,cid,epgid,ptype,precommend,pictype,picurl,spicurl,fid,fid2,js) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
		ProgramDetail program;
		try {
			String cid = String.valueOf(programs.get(0).getCid());
			String[] whereArgs = new String[] { cid };
			// 删表
			int num = db.delete("programs", "cid = ?", whereArgs);
			SLog.d(TAG, "删除了数据的节目标识为===" + "删了" + num + "个");
			for (int i = 0; i < programs.size(); i++) {
				program = programs.get(i);
				bindArgs = new Object[] { program.getPid(),
						program.getPweekday(), program.getDate(),
						program.getLastupdate(), program.getPstime().trim(),
						program.getPftime().trim(), program.getPn(),
						program.getCname(), program.getCid(),
						program.getEpgid(), program.getPtype(),
						program.getPrecommend(), program.getPictype(),
						program.getPicurl(), program.getSpicurl(),
						program.getFid(), program.getFid2(), program.getJs() };
				if (db != null && db.isOpen()) {
					db.execSQL(sql, bindArgs);
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			SLog.i(TAG, e.toString());
		} finally {
			db.endTransaction();
			db.close();
		}

	}

	/**
	 * 要保存节目返回true，
	 * 
	 * @param channel_id
	 *            要判断是否存储节目的频道号
	 * @return
	 */
	public boolean saveProgramsTrue(String channel_id) {
		boolean saveTrue = false;
		db = dbHelper.getWritableDatabase();
		Cursor cursor = null;
		if (db != null && db.isOpen()) {
			String columns[] = new String[] { "pid", "pweekday", "date",
					"lastupdate", "pstime", "pftime", "pn", "epgid", "cid",
					"cname", "ptype", "precommend", "picurl", "fid", "fid2" };
			String selection;
			selection = "cid=? ";
			String[] selectionArgs = new String[] { channel_id };
			cursor = db.query("programs", columns, selection, selectionArgs,
					null, null, null);
			if (cursor != null && cursor.getCount() > 0) {
				saveTrue = false;
			} else {
				saveTrue = true;
			}
			cursor.close();
		}
		db.close();
		return saveTrue;
	}

	/**
	 * 根据频道号查节目
	 * 
	 * @return
	 */
	public ArrayList<ProgramDetail> findProgramById(String channelId) {
		Cursor cursor = null;
		db = dbHelper.getWritableDatabase();
		if (db != null && db.isOpen()) {
			if (TimeUtil.isDeleteProgram(context)) {
				// 删表
				int num = db.delete("programs", null, null);
				SLog.d(TAG, "删除了数据的节目标识为===" + channelId + "删了" + num + "个");
			} else {
				// 查数据库
				// 得到当前周数
				int week = TimeUtil.getWeek();
				int week2 = week + 1;
				if (week2 == 8) {
					week2 = 1;
				}
				String columns[] = new String[] { "pid", "pweekday", "date",
						"lastupdate", "pstime", "pftime", "pn", "epgid", "cid",
						"cname", "ptype", "precommend", "picurl", "fid",
						"fid2", "js" };
				String selection = "cid=? and ((pweekday = ? and (pftime>? or pstime>?)) or pweekday = ?)";
				String currentTime = TimeUtil.localZone2G8("HH:mm:ss",
						new SimpleDateFormat("HH:mm:ss").format(new Date()));
				String[] selectionArgs = new String[] { channelId,
						String.valueOf(week), currentTime, currentTime,
						String.valueOf(week2) };
				String orderBy;
				if (week == 7) {
					// 用降序
					orderBy = "pweekday desc,pstime asc";
				} else {
					// 用升序
					orderBy = "pweekday asc,pstime asc";
				}
				cursor = db.query(true, "programs", columns, selection,
						selectionArgs, null, null, orderBy, null);
			}
		}
		ArrayList<ProgramDetail> programs = null;
		if (cursor != null) {
			programs = getProgramFromDB(cursor);
		} else {
			db.close();
		}
		return programs;
	}

	private ArrayList<ProgramDetail> getProgramFromDB(Cursor cursor) {
		ArrayList<ProgramDetail> programs = new ArrayList<ProgramDetail>();
		ProgramDetail program = null;
		// "pid", "pweekday", "date",
		// "lastupdate", "pstime", "pftime", "pn", "epgid", "cid",
		while (cursor.moveToNext()) {
			program = new ProgramDetail();
			program.setPid(cursor.getString(cursor.getColumnIndex("pid")));
			program.setPweekday(cursor.getInt(cursor.getColumnIndex("pweekday")));
			program.setDate(cursor.getString(cursor.getColumnIndex("date")));
			program.setLastupdate(cursor.getString(cursor
					.getColumnIndex("lastupdate")));
			// 得到的语言时区和设置的同 ,对时间不做处理,否则将时区转换
			String pstime = cursor.getString(cursor.getColumnIndex("pstime"));
			String pftime = cursor.getString(cursor.getColumnIndex("pftime"));

			pstime = TimeUtil.change2LocalZone("HH:mm:ss", pstime);
			pftime = TimeUtil.change2LocalZone("HH:mm:ss", pftime);

			program.setPstime(pstime);
			program.setPftime(pftime);
			program.setPn(cursor.getString(cursor.getColumnIndex("pn")));
			program.setEpgid(cursor.getString(cursor.getColumnIndex("epgid")));
			program.setCid(cursor.getInt(cursor.getColumnIndex("cid")));
			// "cname", "ptype", "precommend", "picurl", "fid", "fid2"
			program.setCname(cursor.getString(cursor.getColumnIndex("cname")));
			program.setPtype(cursor.getString(cursor.getColumnIndex("ptype")));
			program.setPrecommend(cursor.getString(cursor
					.getColumnIndex("precommend")));
			program.setPicurl(cursor.getString(cursor.getColumnIndex("picurl")));
			program.setFid(cursor.getString(cursor.getColumnIndex("fid")));
			program.setFid2(cursor.getString(cursor.getColumnIndex("fid2")));

			programs.add(program);
		}
		cursor.close();
		db.close();
		return programs;
	}

	public synchronized void addProgramItem(ArrayList<ProgramItem> items) {
		db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		Object[] bindArgs = null;
		String sql = "insert into pro_item (node,name,pnode) values (?,?,?) ";
		ProgramItem item;
		try {
			// 删表
			int num = db.delete("pro_item", null, null);
			SLog.d(TAG, "删了" + num + "个节目分类");
			for (int i = 0; i < items.size(); i++) {
				item = items.get(i);
				bindArgs = new Object[] { item.getNode(), item.getName(),
						item.getPnode() };
				if (db != null && db.isOpen()) {
					db.execSQL(sql, bindArgs);
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			SLog.i(TAG, e.toString());
		} finally {
			db.endTransaction();
			db.close();
		}
	}

	public ArrayList<ProgramItem> findFathertItem() {
		Cursor cursor = null;
		db = dbHelper.getWritableDatabase();
		if (db != null && db.isOpen()) {
			// 查数据库
			String columns[] = new String[] { "node", "name", "pnode" };
			String selection = "pnode=?";
			String[] selectionArgs = new String[] { "0" };
			cursor = db.query("pro_item", columns, selection, selectionArgs,
					null, null, null);
		}
		if (cursor != null) {
			return getItemFromDB(cursor);
		} else {
			db.close();
			return null;
		}
	}

	public ArrayList<ProgramItem> findAllProgramItem() {
		Cursor cursor = null;
		db = dbHelper.getWritableDatabase();
		if (db != null && db.isOpen()) {
			if (TimeUtil.isDeleteItem(context)) {
				// 删表
				int num = db.delete("pro_item", null, null);
				SLog.d(TAG, "删除了节目分类，共" + num + "条");
			} else {
				// 查数据库
				String columns[] = new String[] { "node", "name", "pnode" };
				cursor = db.query("pro_item", columns, null, null, null, null,
						null);
			}
		}
		if (cursor != null) {
			return getItemFromDB(cursor);
		} else {
			db.close();
			return null;
		}
	}

	private ArrayList<ProgramItem> getItemFromDB(Cursor cursor) {
		ArrayList<ProgramItem> items = new ArrayList<ProgramItem>();
		ProgramItem item = null;
		while (cursor.moveToNext()) {
			item = new ProgramItem();
			// "node", "name", "pnode"
			item.setNode(cursor.getString(cursor.getColumnIndex("node")));
			item.setName(cursor.getString(cursor.getColumnIndex("name")));
			item.setPnode(cursor.getString(cursor.getColumnIndex("pnode")));

			items.add(item);
		}
		cursor.close();
		db.close();
		return items;
	}

	class DBHelper extends SQLiteOpenHelper {

		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, null, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		}

	}

}
